﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using QLNHASACH.DTO;

namespace QLNHASACH.DataAccessLayer.ObjectDAO
{
    class KhachHangDAO
    {
        //Lấy danh sách khách hàng
        public static DataTable LayDSKhachHang()
        {
            string sql="select * from KHACHHANG";
            return DataAccess.GetDataTable(sql);
        }
        //Thêm khách hàng
        public static bool ThemKhachHang(KhachHangDTO khach)
        {
            string sql =string.Format("INSERT INTO KHACHHANG (TenKhachHang,DiaChi,Email,SDT)"
                        + "VALUES (N'{0}',N'{1}','{2}','{3}')", khach.TenKhachHang,khach.DiaChi,khach.Email,khach.SDT);
            if (DataAccess.ExecuteQuery(sql))
            {
                return true;
            }
            return false;
        }
        //Cập nhật thông tin khách hàng
        public static bool CapNhatKhachHang(KhachHangDTO khach)
        {
            string sql =string.Format("UPDATE KHACHHANG SET TenKhachHang=N'{0}',DiaChi=N'{1}',Email=N'{2}',SDT='{3}' where MaKhachHang={4}"
               ,khach.TenKhachHang,khach.DiaChi,khach.Email,khach.SDT,khach.MaKhachHang);
            if (DataAccess.ExecuteQuery(sql))
            {
                return true;
            }
            return false;
        }
        //Xóa một khách hàng
        public static bool XoaMotKhachHang(KhachHangDTO khach)
        {
            string sql="DELETE  FROM KHACHHANG WHERE MaKhachHang=" + khach.MaKhachHang + "";
            if (DataAccess.ExecuteQuery(sql))
            {
                return true;
            }
            return false;
        }
        //Kiểm tra một khách hàng có tồn tại hay ko
        public static bool KiemTraTonTai(KhachHangDTO khach)
        {
            string sql="select * from KHACHHANG where MaKhachHang=" + khach.MaKhachHang + "";
            if (DataAccess.GetDataTable(sql).Rows.Count > 0)
                return true;
            return false;
        }
        //Tim kiem
        public static DataTable TimTheoTen(string strSearch)
        {
            string sql="select * from KHACHHANG where dbo.ConvertString(TenKhachHang) like dbo.ConvertString('%" + strSearch + "%')";
            DataTable dt=DataAccess.GetDataTable(sql);
            if (dt.Rows.Count > 0)
                return dt;
            else 
                return null;
        }
        public static DataTable TimTheoSDT(string strSearch)
        {
            string sql="select * from KHACHHANG where SDT like '%" + strSearch + "%'";
            DataTable dt=DataAccess.GetDataTable(sql);
            if (dt.Rows.Count > 0)
                return dt;
            else
                return null;
        }
        public static DataTable TimTheoEmail(string strSearch)
        {
            string sql="select * from KHACHHANG where Email like '%" + strSearch + "%'";
            DataTable dt=DataAccess.GetDataTable(sql);
            if (dt.Rows.Count > 0)
                return dt;
            else
                return null;
        }
    }
}
